﻿using System.Data;
using ExampleApplication.Models;
using SQLPXBase;
using SQLPXBase.Factory.Keys.KeyModels;

namespace ExampleApplication.DL
{
    internal class sql : SQLPXBase.SQLProxyBase
    {
        public sql() { }

        internal class cCar:SQLPXBase.SqlProxySubclassBase
        {
            public cCar(SQLProxyBase sqlproxy) : base(sqlproxy)
            {
            }

            public DataRow Load(LongPK id)
            {
                ProxyCommand cmd = sqlProxy.CreateCommandForStoredProcedure("dbo.CAR_Load");
                cmd.AddParameters(id.GetSingleValue);
                return sqlProxy.ExecuteSingleResult(cmd);
            }

            public int Save(Person p)
            {
                ProxyCommand cmd = sqlProxy.CreateCommand("dbo.PERSON_Save ?, ?, ?");
                cmd.AddParameters(p.Id.GetSingleValue, p.Name, p.Surname);
                sqlProxy.ExecuteNoQuery(cmd);

                return (int)cmd.OutputParameters.GetByIndex(0);
            }

            public DataTable ListByOwner(LongPK ownerId)
            {
                string sql = string.Format("SELECT * FROM  dbo.CAR WHERE {0}",
                    ownerId.GeneratePrimaryKeyCondition("OwnerId"));

                ProxyCommand cmd = sqlProxy.CreateCommand(sql);
                return sqlProxy.ExecuteDataTable(cmd);

                //ProxyCommand cmd = sqlProxy.CreateCommand(sql); //"dbo.CAR_ListByOwner");
                //cmd.AddParameters(ownerId.GetSingleValue);
                //return sqlProxy.ExecuteDataTable(cmd);
            }
        }

        internal class cPerson : SQLPXBase.SqlProxySubclassBase
        {
            public cPerson(SQLProxyBase sqlproxy)
                : base(sqlproxy)
            {
            }

            public DataRow Load(LongPK id)
            {
                ProxyCommand cmd = sqlProxy.CreateCommand("dbo.PERSON_Load ?");
                cmd.AddParameters(id.GetSingleValue);
                return sqlProxy.ExecuteSingleResult(cmd);
            }

            public DataTable List()
            {
                ProxyCommand cmd = sqlProxy.CreateCommandForStoredProcedure("dbo.PERSON_List");
                return sqlProxy.ExecuteDataTable(cmd);
            }
        }

        internal class cArticolo : SqlProxySubclassBase
        {
            public cArticolo(SQLProxyBase sqlproxy) : base(sqlproxy)
            {
            }

            public DataRow Load(Articolo.ArtPK pk)
            {
                string sql = "select * from ARTICOLO where " + //"k1 =  @k1 and k2 = @k2 and k3 = @k3";
                             pk.GeneratePrimaryKeyCondition("k1", "k2", "k3");
                ProxyCommand cmd = sqlProxy.CreateCommand(sql);
                return sqlProxy.ExecuteSingleResult(cmd);
            }

            public DataTable List()
            {
                string sql = "Select * from ARTICOLO ";
                ProxyCommand cmd = sqlProxy.CreateCommand(sql);
                return sqlProxy.ExecuteDataTable(cmd);
            }
        }

        internal cCar Car;
        internal cPerson Person;
        internal cArticolo Articolo;

        public sql(MSSQLServerConnectionString connStr) : base(connStr)
        {
            this.Car = new cCar(this);
            this.Person = new cPerson(this);
            this.Articolo = new cArticolo(this);
        }
    }
}
